﻿using DBUtil.MetaData;
using System.Collections.Generic;
using DotNetCommon.Extensions;
using Index = DBUtil.MetaData.Index;
using DotNetCommon;

namespace DBUtil
{
    /// <summary>
    /// 数据库管理对象
    /// </summary>
    public abstract class DBManage
    {
        /// <summary>
        /// 数据库访问对象
        /// </summary>
        protected readonly DBAccess db;

        /// <summary>
        /// 根据DBAccess创建DBManage
        /// </summary>
        /// <param name="db"></param>
        public DBManage(DBAccess db)
        {
            this.db = db;
        }

        #region 获取元数据信息
        /// <summary>
        /// 返回所有的架构/模式
        /// </summary>
        public virtual List<Schema> ShowSchemas()
        {
            var sql = $"select * from INFORMATION_SCHEMA.SCHEMATA";
            var dt = db.SelectDataTable(sql);
            var res = new List<Schema>();
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                res.Add(new Schema()
                {
                    Name = dt.Rows[i]["SCHEMA_NAME"].ToString(),
                    DataBaseName = dt.Rows[i]["CATALOG_NAME"].ToString(),
                    Owner = dt.Rows[i]["SCHEMA_OWNER"].ToString()
                });
            }
            return res;
        }

        #region 表的元数据
        /// <summary>
        /// 返回所有的表(简略信息,不包含 列/索引/约束/触发器等信息)<br/>
        /// 如果 <c>schemaName</c> 为空则返回所有schema下的表
        /// </summary>
        public abstract List<Table> ShowTables(string schemaName = null);

        /// <summary>
        /// 返回当前schema下所有的表(简略信息,不包含 列/索引/约束/触发器等信息)
        /// </summary>
        /// <remarks>注意: 对于mysql,效果同 <seealso cref="ShowTables"/></remarks>
        public abstract List<Table> ShowCurrentSchemaTables();

        /// <summary>
        /// 返回指定表的详细元数据(包含 列/索引/约束/触发器等信息),如:
        /// <list type="bullet">
        /// <item>manage.ShowTableDetail("testdb.dbo.table1"): 指定表所在的db和schema</item>
        /// <item>manage.ShowTableDetail("dbo.table1"): 指定表所在的schema,将限定在当前数据库中</item>
        /// <item>manage.ShowTableDetail("table1"): 仅表名,将限定在当前数据库、当前schema内</item>
        /// </list>
        /// </summary>
        public abstract Table ShowTableDetail(string tableName, EnumTableDetailType detailType = EnumTableDetailType.All);

        /// <summary>
        /// 返回指定表的所有列
        /// <list type="bullet">
        /// <item>manage.ShowTableColumns("testdb.dbo.table1"): 指定表所在的db和schema</item>
        /// <item>manage.ShowTableColumns("dbo.table1"): 指定表所在的schema,将限定在当前数据库中</item>
        /// <item>manage.ShowTableColumns("table1"): 仅表名,将限定在当前数据库、当前schema内</item>
        /// </list>
        /// </summary>
        public abstract List<Column> ShowTableColumns(string tableName);

        /// <summary>
        /// 返回指定表的所有索引
        /// <list type="bullet">
        /// <item>manage.ShowTableIndexes("testdb.dbo.table1"): 指定表所在的db和schema</item>
        /// <item>manage.ShowTableIndexes("dbo.table1"): 指定表所在的schema,将限定在当前数据库中</item>
        /// <item>manage.ShowTableIndexes("table1"): 仅表名,将限定在当前数据库、当前schema内</item>
        /// </list>
        /// </summary>
        public abstract List<Index> ShowTableIndexes(string tableName);

        /// <summary>
        /// 返回指定表的所有触发器
        /// <list type="bullet">
        /// <item>manage.ShowTableTriggers("testdb.dbo.table1"): 指定表所在的db和schema</item>
        /// <item>manage.ShowTableTriggers("dbo.table1"): 指定表所在的schema,将限定在当前数据库中</item>
        /// <item>manage.ShowTableTriggers("table1"): 仅表名,将限定在当前数据库、当前schema内</item>
        /// </list>
        /// </summary>
        public abstract List<Trigger> ShowTableTriggers(string tableName);

        /// <summary>
        /// 返回表的所有约束信息
        /// <list type="bullet">
        /// <item>manage.ShowTableConstraints("testdb.dbo.table1"): 指定表所在的db和schema</item>
        /// <item>manage.ShowTableConstraints("dbo.table1"): 指定表所在的schema,将限定在当前数据库中</item>
        /// <item>manage.ShowTableConstraints("table1"): 仅表名,将限定在当前数据库、当前schema内</item>
        /// </list>
        /// </summary>
        public abstract List<MetaData.Constraint> ShowTableConstraints(string tableName);
        #endregion

        #region 视图元数据
        /// <summary>
        /// 返回用户自定义的所有视图,不含列等信息<br/>
        /// 如果 <c>schemaName</c> 为空则返回所有schema下的视图
        /// </summary>
        public abstract List<View> ShowViews(string schemaName = null);

        /// <summary>
        /// 返回当前schema下用户自定义的所有视图,不含列等信息<br/>
        /// </summary>
        /// <remarks>注意: 对于mysql,效果同 <seealso cref="ShowViews"/></remarks>
        public abstract List<View> ShowCurrentSchemaViews();

        /// <summary>
        /// 返回指定视图的详细元数据
        /// <list type="bullet">
        /// <item>manage.ShowViewDetail("testdb.dbo.view1"): 指定视图所在的db和schema</item>
        /// <item>manage.ShowViewDetail("dbo.view1"): 指定视图所在的schema,将限定在当前数据库中</item>
        /// <item>manage.ShowViewDetail("view1"): 仅视图名,将限定在当前数据库、当前schema内</item>
        /// </list>
        /// </summary>
        public abstract View ShowViewDetail(string viewName);
        #endregion

        #region 存储过程&函数&序列 元数据
        /// <summary>
        /// 返回所有的存储过程(仅用户自定义的)
        /// </summary>
        /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
        /// <returns></returns>
        public abstract List<Procedure> ShowProcedures(string schemaName = null);

        /// <summary>
        /// 返回所有的函数(仅用户自定义的)
        /// </summary>
        /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
        /// <returns></returns>
        public abstract List<Function> ShowFunctions(string schemaName = null);

        /// <summary>
        /// 返回所有的序列
        /// </summary>
        /// <param name="schemaName">所属模式名称,如果为空则返回所有</param>
        /// <returns></returns>
        public abstract List<Sequence> ShowSequences(string schemaName = null);
        #endregion

        #endregion

        #region 修改数据库元数据
        /// <summary>
        /// 重命名表名
        /// </summary>
        public abstract string RenameTableSql(string oldName, string newName = null);

        /// <summary>
        /// 重命名列名
        /// </summary>
        public abstract string RenameColumnSql(string tableName, string oldName, string newName = null);

        /// <summary>
        /// 修改表说明
        /// </summary>
        public abstract string UpdateTableDescriptionSql(string tableName, string description = null);

        /// <summary>
        /// 修改视图说明
        /// </summary>
        public abstract string UpdateViewDescriptionSql(string viewName, string description = null);

        /// <summary>
        /// 修改列说明
        /// </summary>
        public abstract string UpdateColumnDescriptionSql(string tableName, string colName, string description = null);

        /// <summary>
        /// 给指定表添加一列
        /// </summary>
        public abstract string AddColumnSql(string tableName);

        /// <summary>
        /// 删除指定表的指定列
        /// </summary>
        public abstract string DropColumnSql(string tableName, string columnName);

        /// <summary>
        /// 删除指定表
        /// </summary>
        public virtual string DropTableSql(string tableName)
            => $"drop table {db.QuotedName(tableName.IfNullOrEmptyUse($"{db.SchemaName}.TableName"))}";

        /// <summary>
        /// 删除指定表
        /// </summary>
        public abstract string DropTableIfExistSql(string tableName);

        /// <summary>
        /// 修改列是否可为空
        /// </summary>
        public abstract string UpdateColumnNullAbleSql(string tableName, string colName, bool isNullAble);

        /// <summary>
        /// 修改列是否唯一
        /// </summary>
        public abstract string UpdateColumnUniqueSql(string tableName, string colName, bool isUnique);

        /// <summary>
        /// 修改列是否自增
        /// </summary>
        public abstract string UpdateColumnIdentitySql(string tableName, string colName, bool isIdentity);

        /// <summary>
        /// 修改列类型
        /// </summary>
        public abstract string UpdateColumnTypeSql(string tableName, string colName, string typeString = null);

        /// <summary>
        /// 修改列
        /// </summary>
        public abstract string UpdateColumnSql(string tableName, string colName);

        /// <summary>
        /// 设置列默认值
        /// </summary>
        public abstract string UpdateColumnDefaultSql(string tableName, string colName, string defaultString = null);

        /// <summary>
        /// 移除列默认值
        /// </summary>
        public abstract string RemoveColumnDefaultSql(string tableName, string colName);

        /// <summary>
        /// 删除触发器
        /// </summary>
        public virtual string DropTriggerSql(string triggerName)
            => $"drop trigger {db.QuotedName(triggerName.IfNullOrEmptyUse($"{db.SchemaName}.TriggerName"))}";

        /// <summary>
        /// 删除触发器
        /// </summary>
        public abstract string DropTriggerIfExistSql(string triggerName);

        /// <summary>
        /// 删除存储过程
        /// </summary>
        public virtual string DropProcedureSql(string procName)
            => $"drop procedure {db.QuotedName(procName.IfNullOrEmptyUse($"{db.SchemaName}.ProcedureName"))}";

        /// <summary>
        /// 删除存储过程
        /// </summary>
        public abstract string DropProcedureIfExistSql(string procedureName);

        /// <summary>
        /// 修改约束
        /// </summary>
        public abstract string UpdateConstaintSql();

        /// <summary>
        /// 修改索引
        /// </summary>
        public abstract string UpdateIndexSql();

        /// <summary>
        /// 修改触发器
        /// </summary>
        public abstract string UpdateTriggerSql();

        /// <summary>
        /// 设置主键
        /// </summary>
        public abstract string SetPrimaryKeySql(string tableName, params string[] colNames);

        /// <summary>
        /// 删除主键
        /// </summary>
        public abstract string RemovePrimaryKeySql(string tableName);
        #endregion

        #region 生成insert语句
        /// <summary>
        /// 根据表名称和过滤条件生成表数据的insert语句
        /// </summary>
        /// <param name="tableName">表名称</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        public abstract (long count, string sql, long total) GenerateInsertSql(string tableName, int limitCount = 0, string filter = null, IDictionary<string, object> parameters = null);

        /// <summary>
        /// 将表数据全部导出到文件
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="fileAbsPath">文件的绝对路径</param>
        /// <param name="limitCount">限制导出的数量(当数据量很大时使用,0:表示全部导出)</param>
        /// <param name="filter">过滤条件(以and开头)</param>
        /// <param name="parameters">参数</param>
        /// <returns>导出的记录数</returns>
        public abstract long GenerateInsertSqlFile(string tableName, string fileAbsPath, int limitCount = 0, string filter = null, IDictionary<string, object> parameters = null);
        #endregion

        #region truncate指定表
        public virtual string TruncateTableSql(string tableName)
            => $"truncate table {db.QuotedName(tableName.IfNullOrEmptyUse($"{db.SchemaName}.TableName"))}";
        #endregion

        #region 获取记录数
        public virtual long GetCount(string tableName, string filter = null)
        {
            Ensure.NotNullOrEmptyOrWhiteSpace(tableName);
            var sql = $"select count(1) from {db.QuotedName(tableName)} {db.LinkWhereFilterSeg(filter)}";
            return db.SelectScalar<long>(sql);
        }
        #endregion

        #region 生成建表脚本

        public abstract string GenerateCreateTableSql(string tableName);

        public abstract void GenerateCreateTableSqlFile(List<Table> tables, string fileAbsPath, bool includeInsertSql = false);
        #endregion

        #region 根据表生成增删改查代码
        public abstract string GenerateCode(string tableName);
        #endregion
    }
}
